Imports System.Data.OleDb
Imports DTO

Public Class HoaDonDao

    Inherits AbstractDao

    Public Sub New()

    End Sub

    Public Function LayBang() As DataTable

        Dim dt As New DataTable()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From HoaDon"
        ' B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, conn)
        da.FillSchema(dt, SchemaType.Source)
        da.Fill(dt)
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return dt

    End Function

    Public Function LayDanhSach() As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From HoaDon"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim hdDto As New HoaDon_Dto
            hdDto.MaHD = dr("MaHD")
            hdDto.MaPT = dr("MaPT")
            hdDto.NgayTraPhong = dr("NgayTraPhong")
            hdDto.SoNgayThue = dr("SoNgayThue")
            hdDto.TongTien = dr("TongTien")
            ds.Add(hdDto)
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        conn.Close()
        Return ds

    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From HoaDon"
        'B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        ' B5: Dong ket noi CSDL
        cn.Close()

    End Sub

    Public Function TimKiem(ByVal mhd As Integer) As HoaDon_Dto

        Dim hdDto As New HoaDon_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From HoaDon Where MaHD = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaHD", OleDbType.Integer)

        cmd.Parameters("@MaHD").Value = mhd

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            hdDto = New HoaDon_Dto()
            hdDto.MaHD = dr("MaHD")
            hdDto.MaPT = dr("MaPT")
            hdDto.NgayTraPhong = dr("NgayTraPhong")
            hdDto.SoNgayThue = dr("SoNgayThue")
            hdDto.TongTien = dr("TongTien")
        End While
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return hdDto

    End Function

    Public Sub Them(ByVal hdDto As HoaDon_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Insert into HoaDon( MaPT, NgayTraPhong, SoNgayThue,TongTien) values ( ?, ?, ?,?)"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)

        cmd.Parameters.Add("@MaPT", OleDbType.Integer)
        cmd.Parameters.Add("@NgayTraPhong", OleDbType.Date)
        cmd.Parameters.Add("@SoNgayThue", OleDbType.Integer)
        cmd.Parameters.Add("@TongTien", OleDbType.Double)

        cmd.Parameters("@MaPT").Value = hdDto.MaPT
        cmd.Parameters("@NgayTraPhong").Value = hdDto.NgayTraPhong
        cmd.Parameters("@SoNgayThue").Value = hdDto.SoNgayThue
        cmd.Parameters("@TongTien").Value = hdDto.TongTien

        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, conn)
        hdDto.MaHD = CInt(cmd.ExecuteScalar())
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Xoa(ByVal mhd As Integer)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Delete From HoaDon Where MaHD = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@MaHD", OleDbType.Integer)

        cmd.Parameters("@MaHD").Value = mhd

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        cn.Close()

    End Sub

    Public Sub Sua(ByVal hdDto As HoaDon_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Update HoaDon Set MaHD = ? , MaPT = ? , NgayTraPhong= ? , SoNgayThue= ? ,TongTien = ? Where MaHD = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)

        cmd.Parameters.Add("@MaHD", OleDbType.Integer)
        cmd.Parameters.Add("@MaPT", OleDbType.Integer)
        cmd.Parameters.Add("@NgayTraPhong", OleDbType.Date)
        cmd.Parameters.Add("@SoNgayThue", OleDbType.Integer)
        cmd.Parameters.Add("@TongTien", OleDbType.Double)

        cmd.Parameters("@MaPT").Value = hdDto.MaPT
        cmd.Parameters("@NgayTraPhong").Value = hdDto.NgayTraPhong
        cmd.Parameters("@SoNgayThue").Value = hdDto.SoNgayThue
        cmd.Parameters("@TongTien").Value = hdDto.TongTien
        cmd.Parameters("@MaHD").Value = hdDto.MaHD

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

End Class
